﻿using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace ncp.Business
{
    public class ProductInfoBll
    {
        /// <summary>
        /// 得到产品信息表
        /// </summary>
        public static DataTable GetProductInfoTable(string p_Where, string p_Orderby, string pDataBase)
        {
            string sqlStr = "select * from bi_ProductInfo_v";
            if (!string.IsNullOrEmpty(p_Where))
                sqlStr += " where " + p_Where;
            if (!string.IsNullOrEmpty(p_Orderby))
                sqlStr += " order by " + p_Orderby;
            return CommonBll.GetdtList(sqlStr, pDataBase);
        }

        /// <summary>
        /// 得到分页产品信息表
        /// </summary>
        public static DataTable GetProductInfoTable(int piPageIndex, int piPageSize, string pcFieldList, string pcOrderBy, Dictionary<string, object> dicWhere, string pDataBase)
        {
            List<SqlParameter> ilist = new List<SqlParameter>();
            string pcWhere = "1=1 ";
            foreach (KeyValuePair<string, object> kvp in dicWhere)
            {
                if (kvp.Key != "more")
                {
                    pcWhere += " and " + kvp.Key + "=@" + kvp.Key;
                    ilist.Add(new SqlParameter("@" + kvp.Key, kvp.Value));
                }
                else
                {
                    pcWhere += " and " + kvp.Value;
                }
            }
            string sqlStr = CommonBll.GetPagingSQL(piPageIndex, piPageSize, "bi_ProductInfo_v", pcFieldList, "Product_Id", pcOrderBy, pcWhere);
            SqlParameter[] paras = ilist.ToArray();
            return CommonBll.GetdtList(sqlStr, pDataBase, paras);
        }

        /// <summary>
        /// 得到分页产品信息表
        /// </summary>
        public static DataTable GetProductInfoTable(int piPageIndex, int piPageSize, string pcFieldList, string pcOrderBy, string dicWhere, string pDataBase)
        {
            string sqlStr = CommonBll.GetPagingSQL(piPageIndex, piPageSize, "bi_ProductInfo_v", pcFieldList, "Product_Id", pcOrderBy, dicWhere);
            return CommonBll.GetdtList(sqlStr, pDataBase);
        }

        public static DataTable GetProductInfoJoinTable(string fieldList, string orderBy, string sqlWhere, SqlParameter[] paras, string pDataBase)
        {
            string sqlStr = " select " + fieldList + " from bi_ProductInfo as a inner join bi_ClientInfo as b on a.Client_Id=b.Client_Id " + sqlWhere + " " + orderBy;
            return CommonBll.GetdtList(sqlStr, pDataBase, paras);
        }

        //获得产品信息
        public static DataTable GetProductInfoTable(string fieldList, Dictionary<string, object> dicWhere, string order, string pDataBase,bool isSingle)
        {
            List<SqlParameter> ilist = new List<SqlParameter>();
            string pcWhere = " ";
            foreach (KeyValuePair<string, object> kvp in dicWhere)
            {
                if (kvp.Key != "more")
                {
                    pcWhere += " and " + kvp.Key + "=@" + kvp.Key;
                    ilist.Add(new SqlParameter("@" + kvp.Key, kvp.Value));
                }
                else
                {
                    pcWhere += " and " + kvp.Value;
                }
            }
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat(" select {0} from ", fieldList);
            builder.Append(" bi_ProductInfo_v t where  1=1 ");
            if (isSingle)
            {
                builder.AppendFormat(
                    "and  not exists (select 1 from bi_ProductInfo_v where client_id=t.client_id and Product_Id<t.Product_Id {0} ) ",
                    pcWhere);
            }
            builder.Append( pcWhere);
            builder.AppendFormat(" order by case when Vip_EndDate is null or Vip_EndDate<getdate() then '1900-1-1' end,User_Points,{0} Product_StartTime desc", order);

            SqlParameter[] paras = ilist.ToArray();
            return CommonBll.GetdtList(builder.ToString(), pDataBase, paras);
        }

        public static int UpdateProductInfo(Dictionary<string, object> dicPro, Dictionary<string, object> dicWhere, string pDataBase)
        {
            List<SqlParameter> ilist = new List<SqlParameter>();
            string pcWhere = "1=1 ";
            string fields = "";
            if (dicPro.Count == 0)
            {
                return 0;
            }
            foreach (KeyValuePair<string, object> kvp in dicPro)
            {
                fields += "," + kvp.Key + "=@" + kvp.Key;
                ilist.Add(new SqlParameter("@" + kvp.Key, kvp.Value));
            }
            fields = fields.Substring(1);
            foreach (KeyValuePair<string, object> kvp in dicWhere)
            {
                if (kvp.Key != "more")
                {
                    pcWhere += " and " + kvp.Key + "=@" + kvp.Key;

                    ilist.Add(new SqlParameter("@" + kvp.Key, kvp.Value));
                }
                else
                {
                    pcWhere += " and " + kvp.Value;
                }
            }
            string sqlStr = "update bi_ProductInfo set " + fields + " where " + pcWhere;
            SqlParameter[] paras = ilist.ToArray();
            return CommonBll.ExecuteNonQuery(CommandType.Text, sqlStr, pDataBase, paras);
        }

        /// <summary>
        /// 得到记录数
        /// </summary>
        public static int GetProductInfoCount(string pcWhere, string pDataBase)
        {
            string sqlStr = "select count(*) from bi_ProductInfo_v";
            if (!string.IsNullOrEmpty(pcWhere))
                sqlStr += " where " + pcWhere;
            return CommonBll.ReadInteger(sqlStr, pDataBase);
        }

        /// <summary>
        /// 得到记录数
        /// </summary>
        public static int GetProductInfoCount(Dictionary<string, object> dicWhere, string pDataBase)
        {
            string sqlStr = "select count(*) from bi_ProductInfo_v where 1=1 ";
            List<SqlParameter> ilist = new List<SqlParameter>();
            string pcWhere = " ";
            foreach (KeyValuePair<string, object> kvp in dicWhere)
            {
                if (kvp.Key != "more")
                {
                    pcWhere += " and " + kvp.Key + "=@" + kvp.Key;
                    ilist.Add(new SqlParameter("@" + kvp.Key, kvp.Value));
                }
                else
                {
                    pcWhere += " and " + kvp.Value;
                }
            }
            SqlParameter[] paras = ilist.ToArray();
            return CommonBll.ReadInteger(sqlStr+pcWhere, pDataBase, paras);
        }

        /// <summary>
        /// 修改产品信息
        /// </summary>
        public static int UpdateProductInfo(Dictionary<string, object> dicProduct, string pDataBase)
        {
            string sqlStr = "delete bi_PictureInfo where Product_Id=@proid  UPDATE dbo.bi_ProductInfo " +
                            "SET Client_Id = @client_id, " +
                            "G_Id =  @g_id, " +
                            "Product_Title =  @productname, " +
                            "Product_Picture =  @cover," +
                            "Product_Amount = @pronum," +
                            "Product_Place = @detail," +
                            "Product_EndTime =  @end," +
                            "Product_Remark =  @content,Product_AreaId=@area,Product_Unit=@unit" +
                            " where Product_Id=@proid";
            SqlParameter[] paras = new SqlParameter[]
                                       {
                                           new SqlParameter("@g_id", dicProduct["g_id"]),
                                           new SqlParameter("@client_id", dicProduct["client_id"]),
                                           new SqlParameter("@productname", dicProduct["productname"]),
                                           new SqlParameter("@cover", dicProduct["cover"]),
                                           new SqlParameter("@pronum", dicProduct["pronum"]),
                                           new SqlParameter("@detail", dicProduct["detail"]),
                                           new SqlParameter("@end", dicProduct["end"]),
                                           new SqlParameter("@content", dicProduct["content"]),
                                           new SqlParameter("@area", dicProduct["area"]),
                                           new SqlParameter("@proid", dicProduct["proid"]),
                                           new SqlParameter("@unit", dicProduct["unit"])
                                       };
            int indentityId = CommonBll.ExecuteNonQuery(CommandType.Text, sqlStr, "ncp", paras);
            var picList = dicProduct["pic"].ToString();
            if (picList == "")
            {
                return indentityId;
            }
            picList = picList.Substring(1);
            int indent = 0;
            foreach (var o in picList.Split('|'))
            {
                string sql = "insert bi_PictureInfo(Product_Id,Pic_Path,Pic_Note) values(@productId,@picPath,'')";
                SqlParameter[] parameters = new SqlParameter[]
                                           {
                                               new SqlParameter("@productId", dicProduct["proid"]),
                                               new SqlParameter("@picPath", o)
                                           };
                indent = CommonBll.ExecuteNonQuery(CommandType.Text, sql, "ncp", parameters);
            }
            return indent;
        }

        /// <summary>
        /// 增加产品信息
        /// </summary>
        public static int InsertProductInfo(Dictionary<string, object> dicProduct, string pDataBase)
        {
            string sqlStr = "INSERT INTO dbo.bi_ProductInfo (G_Id, Client_Id, Product_Title, Product_Picture, Product_Place, Product_Amount, Product_StartTime, Product_EndTime, Product_Remark,Product_AreaId,Product_Unit) " +
                            "VALUES (@g_id, @client_id, @productname, @cover, @addr, @pronum, getdate(), @end, @content,@area,@unit)";
            SqlParameter[] paras = new SqlParameter[]
                                       {
                                           new SqlParameter("@g_id", dicProduct["g_id"]),
                                           new SqlParameter("@client_id", dicProduct["client_id"]),
                                           new SqlParameter("@productname", dicProduct["productname"]),
                                           new SqlParameter("@cover", dicProduct["cover"]),
                                           new SqlParameter("@pronum", dicProduct["pronum"]),
                                           new SqlParameter("@addr", dicProduct["detail"]),
                                           new SqlParameter("@area", dicProduct["area"]),
                                           new SqlParameter("@end", dicProduct["end"]),
                                           new SqlParameter("@content", dicProduct["content"]),
                                           new SqlParameter("@unit", dicProduct["unit"])
                                       };
            int indentityId = CommonBll.ExecuteNonQuery(CommandType.Text, sqlStr, pDataBase, paras);
            var picList = dicProduct["pic"].ToString();
            if (picList == "")
            {
                return indentityId;
            }
            picList = picList.Substring(1);
            int indent = 0;
            foreach (var o in picList.Split('|'))
            {
                string sql="insert bi_PictureInfo(Product_Id,Pic_Path,Pic_Note) values(@productId,@picPath,'')";
                SqlParameter[] parameters = new SqlParameter[]
                                           {
                                               new SqlParameter("@productId", indentityId),
                                               new SqlParameter("@picPath", o)
                                           };
                indent = CommonBll.ExecuteNonQuery(CommandType.Text, sql, pDataBase, parameters);
            }
            return indent;
        }

        /// <summary>
        /// 删除产品
        /// </summary>
        public static int DeleteProductInfo(string Product_Id, string pDataBase)
        {
            string sqlStr = "delete from bi_ProductInfo where Product_Id in(" + Product_Id + ")  delete from bi_PictureInfo where Product_Id in(" + Product_Id + ")";
            return CommonBll.ExecuteTransaction(sqlStr, pDataBase);
        }

        public static int DeleteProductInfo(string Product_Id,string clientId, string pDataBase)
        {
            string sqlStr = "delete from bi_ProductInfo where Product_Id in(" + Product_Id + ") and Client_Id="+clientId+"  delete from bi_PictureInfo where Product_Id in(" + Product_Id + ")";
            return CommonBll.ExecuteTransaction(sqlStr, pDataBase);
        }

        /// <summary>
        /// 获取商品类别
        /// </summary>
        public static DataTable GetGoodsInfo(string GC_Id,bool status, string pDataBase)
        {
            var g_status = status ? " G_Status=1 and" : " ";
            string sqlStr = "select * from bi_GoodsInfo where "+g_status+" GC_Id=@GC_Id";
            SqlParameter[] paras = new SqlParameter[]
                                       {
                                           new SqlParameter("@GC_Id", GC_Id)
                                       };
            return CommonBll.GetdtList(sqlStr, pDataBase, paras);
        }

        public static  DataTable GetGoodsClass(string parentId,string pDataBase)
        {
            string sqlStr = "select * from bi_GoodsClass where GC_PId=@pid";
            SqlParameter[] paras = new SqlParameter[]
                                       {
                                           new SqlParameter("@pid", parentId)
                                       };
            return CommonBll.GetdtList(sqlStr, pDataBase, paras);
        }

        public static DataTable GetGoodsCategory(string levelId, string pDataBase)
        {
            string sqlStr = "select * from bi_GoodsClass where GC_Level=@levelId";
            SqlParameter[] paras = new SqlParameter[]
                                       {
                                           new SqlParameter("@levelId", levelId)
                                       };
            return CommonBll.GetdtList(sqlStr, pDataBase, paras);
        }

        public static DataTable GetGoodsinfobyId(string id, string pDataBase)
        {
            string sqlStr = "select * from bi_GoodsInfo where G_Id=@id";
            SqlParameter[] paras = new SqlParameter[]
                                       {
                                           new SqlParameter("@id", id)
                                       };
            return CommonBll.GetdtList(sqlStr, pDataBase, paras);
        }

        public static DataTable GetGoodsinfobyPId(string id, string pDataBase)
        {
            string sqlStr = "select * from bi_GoodsInfo where GC_Id=@id";
            SqlParameter[] paras = new SqlParameter[]
                                       {
                                           new SqlParameter("@id", id)
                                       };
            return CommonBll.GetdtList(sqlStr, pDataBase, paras);
        }
        public static DataTable GetGoodsClassbyId(string id, string pDataBase)
        {
            string sqlStr = "select * from bi_GoodsClass where GC_Id=@id";
            SqlParameter[] paras = new SqlParameter[]
                                       {
                                           new SqlParameter("@id", id)
                                       };
            return CommonBll.GetdtList(sqlStr, pDataBase, paras);
        }
        public static DataTable GetGoodsClassByWhere(string where,string pDataBase)
        {
            string sqlStr = "select * from bi_GoodsClass ";
            if (where!="")
            {
                sqlStr = sqlStr + where;
            }
            return CommonBll.GetdtList(sqlStr, pDataBase);
        }

        public  static DataTable GetGoodsClassWhereHasItems(string userId,string pDataBase)
        {
            string sqlStr = "select distinct GC_PId,gcname from dbo.bi_GoodsInfo_v where gclevel=2 and G_Id in (select G_Id from dbo.bi_ProductInfo where Client_Id=" + userId + ")";
            //string sqlStr = "select * from bi_GoodsInfo_v as a where gclevel=2 and (select COUNT(*) from bi_ProductInfo as b where b.G_Id=a.G_Id and Client_Id="+userId+")>0 ";
            return CommonBll.GetdtList(sqlStr, pDataBase);
        }

        //根据ID获得产品图片
        public static DataTable GetProductPic(string id)
        {
            string sql = "select * from bi_PictureInfo where Product_Id=@id";
            SqlParameter[] paras = new SqlParameter[]
                                       {
                                           new SqlParameter("@id", id)
                                       };
            return CommonBll.GetdtList(sql, "ncp", paras);
        }
        /// <summary>
        /// 获取省市县列表
        /// </summary>
        public static DataTable GetAreaList(int type, string pId, string pDataBase)
        {
            string sqlStr = "";
            switch (type)
            {
                case 0:
                    sqlStr = "select distinct provinceID,province from bi_povince_v";
                    break;
                case 1:
                    sqlStr =pId==""?"": "select distinct cityID,city from bi_povince_v where provinceID=" + pId;
                    break;
                case 2:
                    sqlStr = pId == "" ? "" : "select distinct areaID,area from bi_povince_v where cityID=" + pId;
                    break;
            }
            if (sqlStr == "")
            {
                return null;
            }
            return CommonBll.GetdtList(sqlStr, pDataBase);
        }

        /// <summary>
        /// 根据县级ID查询省市县
        /// </summary>
        public static DataTable GetPlaceInfo(string areaId, string pDataBase)
        {
            var sqlStr = "select * from bi_povince_v where areaID=" + areaId;
            return CommonBll.GetdtList(sqlStr, pDataBase);
        }

        //插入搜索关键字
        public static int InsertSearchKey(string key, string ip,int type, string pDataBase)
        {
            SqlParameter[] paras = new SqlParameter[]
                                     {
                                         new SqlParameter("@key",key),
                                         new SqlParameter("@searchIp",ip),
                                         new SqlParameter("@searchType",type)
                                     };
            return CommonBll.ExecuteNonQuery(CommandType.StoredProcedure, "proc_InserSearchKey", pDataBase, paras);
        }

        //搜索热点关键字
        public static DataTable GetHotKeys(string pDataBase)
        {
            SqlParameter[] paras = new SqlParameter[] { };
            return CommonBll.GetdtList(CommandType.StoredProcedure, "proc_getHotKey", pDataBase, paras);
        }
    }
}
